IF OBJECT_ID('dbo.GetUnits') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetUnits
    IF OBJECT_ID('dbo.GetUnits') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetUnits >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetUnits >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetUnits
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 9/11/2007
--
-- Description: Returns list of Departments from AgrAgreement_fct table
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetUnits()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 9/11/07     KC        Created
-- 9/21/07     KC        Added @Department as a Param
-- 9/26/07     KC        Added DeptId as return column
-- 12/17/08    KC        Changed security to new function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
-- 01/07/08    KC        Roll back temp table change.
--
-- =======================================================================
CREATE PROCEDURE dbo.GetUnits
     (@Department                INT=NULL,
     @DomainUserId                 INT )
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     UnitId              INT
     Unit                VARCHAR
     DeptId              INT
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT

     -- Result Query    
     SELECT DISTINCT U.Id AS UnitId,
               U.Name AS Unit,
               U.DepartmentId AS DeptId
     FROM      dbo.AgrAgreement_Info AI INNER JOIN dbo.SvcEntity_FwkDomainOrganization_Map EDOM ON AI.SvcEntityId = EDOM.SvcEntityId 
               INNER JOIN dbo.FwkDomainOrganization U ON U.Id = EDOM.FwkDomainOrganizationId
               INNER JOIN dbo.FwkDomainOrganization D ON D.Id = U.DepartmentId 
               -- Security Issues: Not all Users can see all Agreements and therefore all Departments
               INNER JOIN dbo.fnGetSecurityAgreements(@DomainUserId) AUTH ON AI.AgreementId = AUTH.AgreementId 
     WHERE     U.FwkDomainOrganizationTypeId = 4 AND
               (@Department IS NULL OR U.DepartmentId = @Department)
     ORDER BY U.Name               


     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.GetUnits') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetUnits >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetUnits >>>'
go

GRANT EXECUTE ON dbo.GetUnits TO InsightReport,Insight
go

